

set more off

**********************count number of transactions in each flow******************
foreach x in arr imp dis exp{
set more off
qui use "${hmrc_datadir}\Trade\\`x'.dta" , clear

gen y=-year
sort codseq y
drop y
bysort codseq: gen n=_n==1
replace country=country[_n-1] if n==0
drop n

*gen fiscal year using perref
rename year calendar_year
sum perref
gen trade_year=int(perref/100)
gen trade_month=perref-trade_year*100
count if trade_year!=calendar_year
drop trade_year

gen year=calendar_year if trade_month>3
replace year=calendar_year-1 if trade_month<=3


qui keep if year<2012&year>1999
qui drop if suite!=0
qui drop if traderid=="UNKNOWN"
qui drop if traderid=="BTTA"

qui keep if amend==0
qui drop amend

qui drop if svalue==0
qui drop if netmass==0&suppunit==0

*sort codseq traderid year comcode svalue
*drop codseq

qui gen transaction=1 
bysort traderid year comcode country: egen total_value=total(svalue)
bysort traderid year comcode country: egen n_transaction=total(transaction)

qui keep country traderid year comcode n_transaction total_value
qui duplicates drop
qui save "${hmrc_datadir}\Trade\\`x'_numtransaction", replace
}

*******************append for export/import***********************************
set more off
qui use "${hmrc_datadir}\Trade\arr_numtransaction", replace
append using "${hmrc_datadir}\Trade\imp_numtransaction"
qui save "${hmrc_datadir}\Trade\import_numtransaction", replace

qui use "${hmrc_datadir}\Trade\dis_numtransaction", replace
append using "${hmrc_datadir}\Trade\exp_numtransaction"
qui save "${hmrc_datadir}\Trade\export_numtransaction", replace


************************merge number of transactions with final data set********
foreach x in export import{
set more off
qui use "${hmrc_datadir}\Trade\\`x'_numtransaction", clear
*****merge with country iso code
replace country="Uae" if country=="UAE"
replace country="Us Virgin Is" if country=="US Virgin Is"
replace country="Us Minor Islands" if country=="US Minor Islands"
replace country="Fyr Macedonia" if country=="FYR Macedonia"
merge m:1 country using  "${hmrc_datadir}\Trade\iso_table.dta"
drop if _merge==2
drop _merge alpha2code numeric

*****merge with taxpayer id:
merge 1:m traderid comcode country year using "${hmrc_datadir}\Lookup tables\fy lookup_mergetrade.dta"
keep if _merge==3
drop _merge
keep traderid comcode country year total_value n_transaction alpha3code identifier

* add up the number of transaction for each taxpayer (firm)
bysort identifier comcode alpha3code year: egen no_trans=total(n_transaction)
keep identifier comcode alpha3code year no_trans

duplicates drop
save "${hmrc_datadir}\Trade\\`x'_numtransaction_id", replace
}


foreach x in export import{
use "${hmrc_datadir}\Trade\\`x'_numtransaction_id", replace

forv y=2005/2011{
preserve
keep if year==`y'
save "${hmrc_datadir}\Trade\\`x'_numtransaction_id`y'",replace
restore
drop if year==`y'
}
}

foreach x in export import{
set more off
use "${hmrc_datadir}\\`x'_reg_final_ukmncs_fy", clear

forv y=2005/2011{
preserve
keep if year==`y'
merge 1:m identifier year comcode alpha3code ///
using "${hmrc_datadir}\Trade\\`x'_numtransaction_id`y'"

drop if _merge==2
drop _merge
save "${hmrc_datadir}\\`x'_reg_final_ukmncs_fy_notrans`y'",replace
restore
drop if year==`y'
}

use "${hmrc_datadir}\\`x'_reg_final_ukmncs_fy_notrans2005",clear
forv y=2006/2011{
append using "${hmrc_datadir}\\`x'_reg_final_ukmncs_fy_notrans`y'"
}

save "${hmrc_datadir}\\`x'_reg_final_ukmncs_fy_ntrans", replace
}


